﻿using Mes_app;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Mes_app_control
{
    internal class ControlCommandDal
    {
        /// <summary>
        /// 新增
        /// </summary>
        /// <returns></returns>
        public int Add(ControlCommand controlCommand)
        {
            //id,machine_no,machine_name,machine_address,command_param,supplier_no,create_time,status,exec_time
            string sqlValue = "INSERT INTO control_command(machine_no,machine_name,machine_address,command_param,supplier_no,create_time,command_value,description,machine_port,cnc_num )  " +
                " VALUES(@machine_no,@machine_name,@machine_address,@command_param,@supplier_no,getdate(),@command_value,@description,@machine_port,@cnc_num )";

            SqlParameter[] parameters = {
                  new SqlParameter("@machine_no",controlCommand.machine_no),
                  new SqlParameter("@machine_name",controlCommand.machine_name),
                  new SqlParameter("@machine_address",controlCommand.machine_address),
                  new SqlParameter("@command_param",controlCommand.command_param),
                  new SqlParameter("@supplier_no",controlCommand.supplier_no),
                  new SqlParameter("@command_value",controlCommand.command_value),
                  new SqlParameter("@description",controlCommand.description),
                  new SqlParameter("@machine_port",controlCommand.machine_port),
                  new SqlParameter("@cnc_num",controlCommand.cnc_num),
                  
                };

            int count_id = SqlHelper.ExecuteNonQuery(sqlValue, parameters);
            return count_id;

        }

        /// <summary>
        /// 获取DataSet
        /// </summary>
        /// <returns></returns>
        public DataSet GetDataSet(string date1, string date2, ControlCommand controlCommand)
        {
            //id,machine_no,machine_name,machine_address,command_param,supplier_no,create_time,status,exec_time
            String where_str = "";
            if (controlCommand != null)
            {
                if (!string.IsNullOrEmpty(controlCommand.machine_no))
                    where_str += " AND machine_no = '" + controlCommand.machine_no+"'";
                if (!string.IsNullOrEmpty(controlCommand.machine_name))
                    where_str += " AND machine_name = '" + controlCommand.machine_name + "'";
                if (!string.IsNullOrEmpty(controlCommand.command_param))
                    where_str += " AND command_param = '" + controlCommand.command_param + "'";
                if (!string.IsNullOrEmpty(controlCommand.supplier_no))
                    where_str += " AND supplier_no = '" + controlCommand.supplier_no + "'";
                if (!string.IsNullOrEmpty(controlCommand.machine_address))
                    where_str += " AND machine_address = '" + controlCommand.machine_address + "'";
                if (controlCommand.status != 3)
                    where_str += " AND status = '" + controlCommand.status + "'";

            }
            string sqlvalue = "SELECT id,status,machine_no,machine_name,machine_address,command_param,command_value," +
                " supplier_no,create_time,exec_time,description,machine_port,cnc_num " +
                " FROM control_command a " +
                " WHERE 1=1 " + where_str +
                " AND a.create_time >= cast(convert(varchar(10),@date1,120) as datetime) " + //当天00:00:00
                " AND a.create_time <= DATEADD(SS,-1,DATEADD(DD,1,CONVERT(DATETIME,CONVERT(VARCHAR(10),@date2,120))))  " + //当天23:59:59
                " ";
            SqlParameter[] parameters = {
                  new SqlParameter("@date1",date1),
                  new SqlParameter("@date2",date2),
                };
            DataSet ds = SqlHelper.ExecuteDataSet(sqlvalue, parameters);
            return ds;
        }


        /// <summary>
        /// 获取对象
        /// </summary>
        /// <returns></returns>
        public ControlCommand Get(int id)
        {
            ControlCommand controlCommand = new ControlCommand();
            string sqlvalue = "SELECT id,machine_no,machine_name,machine_address,command_param,supplier_no,create_time," +
                " status,exec_time,command_value,description,machine_port,cnc_num " +
                " FROM control_command a " +
                " WHERE a.id  = @id  ";
            SqlParameter[] parameters = {
                  new SqlParameter("@id",id),
                };
            SqlDataReader objReader = SqlHelper.ExecuteReader(sqlvalue, parameters);
            while (objReader.Read())
            {
                controlCommand.id = Convert.ToInt32(objReader["id"]);
                controlCommand.machine_no = objReader["machine_no"].ToString();
                controlCommand.machine_name = objReader["machine_name"].ToString();
                controlCommand.machine_address = objReader["machine_address"].ToString();
                controlCommand.command_param = objReader["command_param"].ToString();
                controlCommand.supplier_no = objReader["supplier_no"].ToString();
                controlCommand.status = Convert.ToInt32(objReader["status"].ToString());
                controlCommand.create_time = Convert.ToDateTime(objReader["create_time"]);
                if (objReader["exec_time"] != DBNull.Value)
                {
                    controlCommand.exec_time = Convert.ToDateTime(objReader["exec_time"]);
                }
                controlCommand.command_value = objReader["command_value"].ToString();
                controlCommand.description = objReader["description"].ToString();
                controlCommand.machine_port = Convert.ToInt32(objReader["machine_port"]);

                if (objReader["cnc_num"] != DBNull.Value)
                    controlCommand.cnc_num = Convert.ToInt32(objReader["cnc_num"]);
                else
                    controlCommand.cnc_num = 0;
  
            }
            objReader.Close();
            return controlCommand;

        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <returns></returns>
        public int Delete(int id)
        {
            string sqlValue = " DELETE FROM control_command WHERE id=@id  ";
            SqlParameter[] parameters = {
                  new SqlParameter("@id",id),
                };

            int count_id = SqlHelper.ExecuteNonQuery(sqlValue, parameters);
            return count_id;
        }


        //id,machine_no,machine_name,machine_address,command_param,supplier_no,create_time,status,exec_time
        /// <summary>
        /// 更新
        /// </summary>
        /// <returns></returns>
        public int Update(ControlCommand controlCommand)
        {
            string sqlValue = "UPDATE control_command SET machine_no = @machine_no," +
                " machine_name = @machine_name, " +
                " machine_address = @machine_address, " +
                " command_param = @command_param," +
                " command_value = @command_value, " +
                " supplier_no = @supplier_no," +
                " description = @description,  " +
                " machine_port = @machine_port," +
                " cnc_num = @cnc_num  " +
                " WHERE id =@id ";
            // listView1.Items.Add(sqlValue);
            SqlParameter[] parameters = {
                  new SqlParameter("@id",controlCommand.id),
                  new SqlParameter("@machine_no",controlCommand.machine_no),
                  new SqlParameter("@machine_name",controlCommand.machine_name),
                  new SqlParameter("@machine_address",controlCommand.machine_address),
                  new SqlParameter("@command_param",controlCommand.command_param),
                  new SqlParameter("@command_value",controlCommand.command_value),
                  new SqlParameter("@supplier_no",controlCommand.supplier_no),
                  new SqlParameter("@description",controlCommand.description),
                  new SqlParameter("@machine_port",controlCommand.machine_port),
                  new SqlParameter("@cnc_num",controlCommand.cnc_num)
                };
            int count_id = SqlHelper.ExecuteNonQuery(sqlValue, parameters);
            return count_id;
        }


        /// <summary>
        /// 获取所有测试标准
        /// </summary>
        /// <returns></returns>
        public List<ControlCommand> GetList()
        {
            string sqlvalue = "SELECT id,machine_no,machine_name,machine_address,command_param,supplier_no,create_time," +
                " status,exec_time,command_value,description,machine_port,cnc_num " +
                " FROM control_command a " +
                " WHERE 1=1 "; ;
            SqlParameter[] parameters = {
                  new SqlParameter("@sid","")
                };
            SqlDataReader objReader = SqlHelper.ExecuteReader(sqlvalue, parameters);

            List<ControlCommand> list = new List<ControlCommand>();
            while (objReader.Read())
            {
                list.Add(new ControlCommand()
                {
                    id = GetDbValue<int>(objReader["id"]),
                    machine_no = objReader["machine_no"].ToString(),
                    machine_address = objReader["machine_address"].ToString(),
                    command_param = objReader["command_param"].ToString(),
                    supplier_no = objReader["supplier_no"].ToString(),
                    create_time = GetDbValue<DateTime>(objReader["create_time"], DateTime.MinValue),
                    status = GetDbValue<int>(objReader["status"], 0),
                    exec_time = GetDbValue<DateTime>(objReader["exec_time"], DateTime.MinValue),
                    command_value = objReader["command_value"].ToString(),
                    description = objReader["description"].ToString(),
                    machine_port = GetDbValue<int>(objReader["machine_port"]),
                    cnc_num = GetDbValue<int>(objReader["cnc_num"]),
                    
                });
            }
            objReader.Close();
            return list;
        }

        private T GetDbValue<T>(object dbValue, T defaultValue = default(T))
        {
            if (dbValue == null || dbValue == DBNull.Value)
                return defaultValue;

            if (typeof(T) == typeof(int))
                return (T)(object)Convert.ToInt32(dbValue);

            if (typeof(T) == typeof(DateTime))
                return (T)(object)Convert.ToDateTime(dbValue);

            return (T)dbValue;
        }


        /// <summary>
        /// 更新
        /// </summary>
        /// <returns></returns>
        public int UpdateStatus(ControlCommand controlCommand)
        {
            string sqlValue = "UPDATE control_command SET status = @status,exec_time = getdate() " +
                " WHERE id =@id ";
            // listView1.Items.Add(sqlValue);
            SqlParameter[] parameters = {
                  new SqlParameter("@id",controlCommand.id),
                  new SqlParameter("@status",controlCommand.status),
                };
            int count_id = SqlHelper.ExecuteNonQuery(sqlValue, parameters);
            return count_id;
        }



    }

}
